---
title: 'MySQL'
date: '2023-05-04 18:34:50'
images: ['https://kodo.huixiangwuyou.com/blog/images/mysql.jpeg']
summary: 'MySQL 是一个开源的关系型数据库管理系统（RDBMS），由瑞典公司 MySQL AB 开发，目前由 Oracle 公司维护。MySQL 是最流行的关系型数据库之一，广泛应用于 Web 应用、企业级系统和嵌入式系统中。它以高性能、可靠性和易用性著称，支持多种操作系统（如 Linux、Windows、macOS）和编程语言（如 Python、Java、PHP 等）'
tags: 
 - SQLDB
---

### 安装

[MySQL 官网](https://www.mysql.com/)  
[下载选择](https://dev.mysql.com/downloads/)   根据个人系统选择下载   
![下载选择](https://kodo.huixiangwuyou.com/blog/images/sql/20250314221124.png)   
[centos7 安装 MySQL](https://blog.huixiangwuyou.com/react/#/linux/mysql)

当前使用的是Debian系统，所以选择使用apt安装，但是在仓库没有指定的版本，所以到 [下载页面](https://dev.mysql.com/downloads/mysql/) 页面跳转 [归档](https://downloads.mysql.com/archives/community/) ,选择对应的归档文件选项下载     
![版本选择](https://kodo.huixiangwuyou.com/blog/images/sql/20250314221700.png)
![版本选择](https://kodo.huixiangwuyou.com/blog/images/sql/20250314223719.png)
选择第一个版本下载，下载完成后使用命令安装

> 可以直接到 https://downloads.mysql.com/archives/installer 页面选定windows系统直接MSI文件安装

``` bash
# 链接可以鼠标右键获取下载链接
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-server_8.0.15-1debian12_amd64.deb-bundle.tar

# 解压到指定目录
tar -xvf mysql-server_8.0.15-1debian12_amd64.deb-bundle.tar -C /usr/local/mysql/

# 进入目录
cd /usr/local/mysql/

# 安装依赖 否则在安装下面的文件会报错
sudo apt-get update
sudo apt-get install libaio1 libmecab2

# 安装下列几个文件 MySQL的密码可以设置为空
sudo dpkg -i mysql-common_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-client-plugins_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-client-core_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-client_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-client_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-server-core_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-community-server_8.0.15-1debian12_amd64.deb
sudo dpkg -i mysql-server_8.0.15-1debian12_amd64.deb

# 验证是否安装成功
mysql -V

# 安装完成后会提示输入密码，默认密码为空，直接回车即可
# 进入mysql 输入密码
mysql -u root -p

```

> 若是安装失败，可以尝试重新安装，或者查看错误日志，查看错误原因
```
# 移除已安装的 MySQL 包：
sudo dpkg --purge mysql-community-server-core mysql-community-server mysql-server

# 重新安装所有 .deb 文件：
sudo dpkg -i *.deb
sudo apt-get install -f

```

#### 账户密码新增修改
``` bash
# 进入mysql 输入密码
mysql -u root -p

# 输入密码后，进入mysql 命令行 修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY '你的新密码';
# 查看用户
select user,host from mysql.user;

# 新增用户 new_user-用户名  password-密码
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

# 修改密码
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

# 刷新权限
flush privileges;

# 退出mysql
exit;

```
![账户登录](https://kodo.huixiangwuyou.com/blog/images/sql/20250315214051.png)

### 常用命令
常见命令基本和SQL一致，这里只列举一些常用的命令,记得在后面加 `;` ,否则MySQL会以为命令还没结束，会一直等待输入，导致命令执行失败。
<br/>

| 分类 | 全称 | 描述 |
| --- | --- | --- |
| DDL | Data Definition Language |数据定义语言，用来定义数据库对象(数据库，表，字段)|
| DML | Data Manipulation Language | 数据指作语言，用来对数据库表中的数据进行增制改 |
| DQL | Data Query Language |数据询语言，用来查询数据库中表的记录|
| DCL | Data Control Language | 数据空制语言，用来创建数据库用户、控制数据库的访问权限 |
|  |  |  |

> 相关命令关联
- DDL
  - CREATE：创建数据库对象，如数据库、表、视图等。
  - ALTER：修改数据库对象的结构，如修改表的列、添加索引等。
  - DROP：删除数据库对象，如删除表、视图等。
  - TRUNCATE：清空表中的数据，保留表的结构。
  - RENAME：重命名数据库对象，如重命名表、视图等。
  - COMMENT：添加注释到数据库对象，如添加表的注释。
  - SHOW：显示数据库对象的信息，如显示表的结构、索引等。
  - DESCRIBE：显示表的结构，与 SHOW TABLE STATUS 类似。
  - EXPLAIN：分析查询语句的执行计划，用于优化查询性能。
  - USE：切换当前数据库。
- DML
  - INSERT：向表中插入数据。
  - UPDATE：更新表中的数据。
  - DELETE：删除表中的数据。
  - MERGE：将数据插入或更新到表中。
  - LOAD DATA：从文件中加载数据到表中。
  - CALL：调用存储过程或函数。
- DQL
  - SELECT：从表中查询数据。
  - FROM：指定查询的表。
  - WHERE：指定查询条件。
  - GROUP BY：对查询结果进行分组。
  - HAVING：对分组后的结果进行过滤。
  - ORDER BY：对查询结果进行排序。
  - LIMIT：限制查询结果的数量。
  - JOIN：连接多个表进行查询。
  - UNION：将多个查询结果合并。
  - INTERSECT：获取两个查询结果的交集。
  - EXCEPT：获取两个查询结果的差集。
- DCL
  - GRANT：授予用户权限。
  - REVOKE：撤销用户权限。
  - CREATE USER：创建新用户。

<br/>

#### 查看数据库
``` bash
# 列出 MySQL 服务器上的所有数据库：
SHOW DATABASES;

# 创建数据库
CREATE DATABASE database_name;
# 没有就创建数据库，有就不创建
CREATE DATABASE if not exits database_name;
# 不要创建 utf8，因为utf8是3字节的，有些数据是4字节的，会导致数据丢失
# 可以使用 utf8mb4，utf8mb4是4字节的，兼容utf8
CREATE DATABASE  database_name default charset utf8mb4;

# 删除数据库  慎用
DROP DATABASE database_name;


#  切换到某个数据库
USE database_name;

# 查看当前数据库中的所有表
SHOW TABLES;

# 查看表结构
DESCRIBE table_name;
# 查看表数据更详细的命令
SHOW CREATE TABLE table_name;

# 查看数据库大小
SELECT table_schema AS "Database",   ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"  FROM information_schema.TABLES  GROUP BY table_schema;  
  
# 查看表的大小
# SELECT table_name AS "Table",  ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"   FROM information_schema.TABLES WHERE table_schema = 'database_name' ORDER BY (data_length + index_length) DESC;   
  
# 查看数据库的字符集和排序规则
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = 'database_name';   
 

# 查看当前连接的数据库
SELECT DATABASE();
  
# 查看 MySQL 状态
SHOW STATUS;

# 查看 MySQL 变量
SHOW VARIABLES;

# 查看当前连接的用户
SELECT USER();

# 查看当前连接的信息
SHOW PROCESSLIST;

# 查看 MySQL 版本
SELECT VERSION();

```
![测试](https://kodo.huixiangwuyou.com/blog/images/sql/20250315230258.png)

#### 创建库表数据 
``` bash
# 创建数据库
CREATE DATABASE blog;

# 创建一个博客评论表
CREATE TABLE comments (
  id INT AUTO_INCREMENT PRIMARY KEY,          -- 评论 ID，主键
  content TEXT NOT NULL,                      -- 评论内容
  user_id INT NOT NULL,                       -- 评论者用户 ID
  username VARCHAR(100) NOT NULL,             -- 评论者用户名（冗余字段，避免频繁查询用户表）
  post_id INT NOT NULL,                       -- 评论所属的文章 ID
  parent_id INT DEFAULT NULL,                 -- 父评论 ID（用于支持嵌套评论）
  status ENUM('pending', 'approved', 'deleted') DEFAULT 'pending', -- 评论状态
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 评论创建时间
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 评论更新时间
  metadata JSON                               -- 扩展字段（JSON 格式，支持灵活扩展）
);

# 模板
CREATE TABLE 表名 (
  字段1 数据类型 [comment '字段1注释'],
  字段2 数据类型 [comment '字段2注释'],
  ...
  字段n 数据类型 [comment '字段n注释']
)[comment '表注释'];
# 注意:[..]为可选参数，最后一个字段后面没有逗号


# 查看表字段
DESC 表名;
+------------+--------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field      | Type                                 | Null | Key | Default           | Extra
             |
+------------+--------------------------------------+------+-----+-------------------+-----------------------------------------------+
| id         | int                                  | NO   | PRI | NULL              | auto_increment
             |
| content    | text                                 | NO   |     | NULL              |
             |
| user_id    | int                                  | NO   |     | NULL              |
             |
| username   | varchar(100)                         | NO   |     | NULL              |
             |
| post_id    | int                                  | NO   |     | NULL              |
             |
| parent_id  | int                                  | YES  |     | NULL              |
             |
| status     | enum('pending','approved','deleted') | YES  |     | pending           |
             |
| created_at | timestamp                            | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED
             |
| updated_at | timestamp                            | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| metadata   | json                                 | YES  |     | NULL              |
             |
+------------+--------------------------------------+------+-----+-------------------+-----------------------------------------------+


# 查询创建表的语句
show create table 表名;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table



                                                                                        |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| comments | CREATE TABLE `comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  `user_id` int NOT NULL,
  `username` varchar(100) NOT NULL,
  `post_id` int NOT NULL,
  `parent_id` int DEFAULT NULL,
  `status` enum('pending','approved','deleted') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `metadata` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# ENGINE=InnoDB 表示使用 InnoDB 存储引擎，AUTO_INCREMENT=3 表示自增主键从 3 开始，CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 表示字符集为 utf8mb4，排序规则为 utf8mb4_0900_ai_ci。


# 直接添加字段
ALTER TABLE comments ADD  new_column_name data_type;
ALTER TABLE comments ADD  like_count INT DEFAULT 0;

# 修改字段类型
ALTER TABLE comments MODIFY  column_name new_data_type;
ALTER TABLE comments MODIFY  like_count INT;

# 修改字段名称
ALTER TABLE comments CHANGE  old_column_name new_column_name data_type;
ALTER TABLE comments CHANGE  like_count likes INT;

# 删除字段
ALTER TABLE comments DROP  column_name;
ALTER TABLE comments DROP  likes;



# 插入一条顶级评论
INSERT INTO comments (content, user_id, username, post_id, status, metadata)
VALUES ('这是一条评论内容', 1, 'john_doe', 101, 'approved', '{"ip": "192.168.1.1", "device": "iPhone"}');

# 插入一条嵌套评论
INSERT INTO comments (content, user_id, username, post_id, parent_id, status, metadata)
VALUES ('这是对第一条评论的回复', 2, 'jane_doe', 101, 1, 'approved', '{"ip": "192.168.1.2", "device": "Android"}');

# 插入模板
INSERT INTO 表名 (字段1, 字段2, ..., 字段n) VALUES (值1, 值2,..., 值n);
# 注意:字段和值的顺序要对应，最后一个字段后面没有逗号 插入的值要求在字段数据范围中

#  给表中所有字段依次赋值
INSERT INTO 表名  VALUES (值1, 值2,..., 值n);

# 批量插入
INSERT INTO 表名 (字段1, 字段2,..., 字段n) VALUES (值1, 值2,..., 值n), (值1, 值2,..., 值n), ..., (值1, 值2,..., 值n);
INSERT INTO 表名 VALUES (值1, 值2,..., 值n), (值1, 值2,..., 值n), ..., (值1, 值2,..., 值n);



# 更新数据
UPDATE 表名 SET 字段1=值1, 字段2=值2,..., 字段n=值n WHERE 条件;
UPDATE 表名 SET 字段1=值1, 字段2=值2,..., 字段n=值n; # 不添加条件会更新所有数据

# 批量更新
UPDATE 表名 SET 字段1=值1, 字段2=值2,..., 字段n=值n WHERE 条件1 OR 条件2 OR ... OR 条件n;

# 删除数据
DELETE FROM 表名 WHERE 条件;
DELETE FROM 表名; # 不添加条件会删除所有数据


# 查询某篇文章的所有评论
SELECT * FROM comments WHERE post_id = 101;

# 查询某篇文章的顶级评论
SELECT * FROM comments WHERE post_id = 101 AND parent_id IS NULL;

# 查询某条评论的所有回复
SELECT * FROM comments WHERE parent_id = 1;



# 修改表名
ALTER TABLE comments RENAME TO new_table_name;

# 删除表 两种方式
DROP TABLE [ if exits ] comments;
TRUNCATE TABLE comments; # 删除表，重新创建表

# 使用 metadata 字段扩展
# 将新字段存储在 metadata 字段中，避免修改表结构。例如，存储评论的点赞数
UPDATE comments SET metadata = JSON_SET(metadata, '$.like_count', 10) WHERE id = 1;

```


#### 创建用户 
``` bash
# 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'soaeon'@'localhost' IDENTIFIED BY 'password';
 
# 查看用户权限 将 username 替换为实际的用户名，host 替换为用户的主机（如 localhost 或 %）
SHOW GRANTS FOR 'username'@'host';
# 为用户授予权限 
GRANT CREATE, INSERT, UPDATE, DELETE ON *.* TO 'soaeon'@'localhost';
# CREATE：允许用户创建数据库和表。
# INSERT：允许用户向表中插入数据。
# UPDATE：允许用户更新表中的数据。
# DELETE：允许用户删除表中的数据。
# DROP：允许用户删除数据库和表。
# SELECT：允许用户查询表中的数据。
# *.*：表示这些权限适用于所有数据库和所有表。你也可以指定具体的数据库和表，例如 database_name.* 表示适用于指定数据库中的所有表。
# 追加权限  SELECT-查看选择权限
GRANT SELECT ON *.* TO 'soaeon'@'localhost';
# 刷新权限
FLUSH PRIVILEGES;
# 撤销权限 也要 FLUSH PRIVILEGES; 刷新权限
REVOKE SELECT ON 数据库名.表名 FROM '用户名'@'主机';
REVOKE SELECT DELETE ON *.* FROM 'soaeon'@'localhost';


# 用户的权限也可以授权所有权限
GRANT ALL PRIVILEGES ON *.* TO 'soaeon'@'localhost';

```
> 特殊情况，已经给用户授权了所有权限，但是撤销某一数据库权限的时候，会报错
```sql
# 网上解决办法：先撤销所有权限，然后再授权  结果还是不行
GRANT ALL PRIVILEGES ON database_name.* TO 'soaeon'@'localhost';
REVOKE ALL PRIVILEGES ON database_name.* FROM'soaeon'@'localhost';

# 最好的是先撤销全部权限，然后再授权 以最小权限为原则
REVOKE ALL PRIVILEGES ON *.* FROM'soaeon'@'localhost';
GRANT SELECT ON database_name1.* TO'soaeon'@'localhost';
GRANT all ON database_name2.* TO'soaeon'@'localhost';
```




### 开启远程可视化访问
`如果是家用电脑要记得在路由器防火墙开放3306端口`
```bash
# 开放3306端口
sudo ufw allow 3306
# 查看3306端口是否开放
sudo netstat -tulpn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      812/sshd: /usr/sbin
tcp6       0      0 :::3306                 :::*                    LISTEN      1514934/mysqld
tcp6       0      0 :::33060                :::*                    LISTEN      1514934/mysqld
tcp6       0      0 :::22                   :::*                    LISTEN      812/sshd: /usr/sbin


# 给远程的用户设置权限
# 查看用户权限
SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| soaeon           | localhost |
+------------------+-----------+
# 给远程的用户设置权限
UPDATE mysql.user SET host = '%' WHERE user = 'soaeon';
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| soaeon           | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
# host为% 表示所有主机都可以访问

# 或者在创建的时候设置外部访问的权限
CREATE USER 'soaeon'@'%' IDENTIFIED BY 'password';

# 刷新权限
FLUSH PRIVILEGES;



# 修改 mysql 的配置文件
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# 开放ipv4和ipv6访问  找到 bind-address 设置
[mysqld]
bind-address = ::
# 使用ipv6的报错 ：IP address '240e:47c:ca9:57d:25da:273a:e0b0:f2d9' could not be resolved: Name or service not known
# 因为使用了IPV6 会用到解析所以还要设置
[mysqld]
....
skip-name-resolve
# 保存退出 然后重启mysql服务

# 重启mysql服务
sudo systemctl restart mysql
sudo systemctl enable mysql
``` 
接下来就可以在Navicat Permium中连接了     
![可视化](https://kodo.huixiangwuyou.com/blog/images/sql/20250317000843.png)    
![可视化](https://kodo.huixiangwuyou.com/blog/images/sql/20250317001023.png)